{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Работа с ClickHouse\n",
    "\n",
    "ClickHouse поддерживает работу различными интерфейсами, включая HTTP, JDBC, ODBC, а также множество сторонних библиотек для популярных языков программирования. Но в данном туториале мы будем использовать нативный клиент, работающий через TCP. Так будет гораздо нагляднее.\n",
    "\n",
    "### Интерфейс командной строки\n",
    "\n",
    "ClickHouse server уже запущен внутри Docker контейнера. Для подключения достаточно ввести консольную команду\n",
    "\n",
    "```bash\n",
    "$ clickhouse client --host 127.0.0.1\n",
    "```\n",
    "\n",
    "В результате ClickHouse покажет приглашение для ввода команд\n",
    "```\n",
    ":)\n",
    "```\n",
    "\n",
    "Напишем запрос \"Hello, world!\"\n",
    "```SQL\n",
    ":) SELECT 'Hello, world!'\n",
    "```\n",
    "\n",
    "Если все работает, переходим к следующему шагу.\n",
    "\n",
    "### ClickHouse как калькулятор\n",
    "\n",
    "Выполним простейшие вычисления. \n",
    "\n",
    "```SQL\n",
    ":) SELECT 2 + 2 * 2\n",
    "```\n",
    "\n",
    "```SQL\n",
    ":) SELECT cos(pi() / 3)\n",
    "```\n",
    "\n",
    "```SQL\n",
    ":) SELECT pow(e(), pi())\n",
    "```\n",
    "Таблица system.numbers содержит единственный столбец number. В этом столбце записана бесконечная последовательность целых положительных чисел. Посмотрим на первые 10\n",
    "\n",
    "```SQL\n",
    ":) SELECT number FROM system.numbers LIMIT 10\n",
    "```\n",
    "Теперь посчитаем сумму квадратов первых 100 натуральных чисел\n",
    "```SQL\n",
    ":) SELECT sum(pow(number, 2))\n",
    "FROM \n",
    "(\n",
    "    SELECT *\n",
    "    FROM system.numbers \n",
    "    LIMIT 101\n",
    ") \n",
    "```\n",
    "Последний пример был написан при помощи **подзапроса** из таблицы system.numbers. Сначала выбираем единственный столбец number с числами от 0 до 100, затем возводим в квадрат и суммируем с использованием **агрегатной функции** sum.\n",
    "\n",
    "## Создание таблицы и загрузка данных\n",
    "Создадим таблицу для обучающей выборки и загрузим туда данные.\n",
    "Таблица для обучающей выборки:\n",
    "```SQL\n",
    ":) CREATE TABLE amazon_train\n",
    "(\n",
    "    date Date MATERIALIZED today(), \n",
    "    ACTION UInt8, \n",
    "    RESOURCE UInt32, \n",
    "    MGR_ID UInt32, \n",
    "    ROLE_ROLLUP_1 UInt32, \n",
    "    ROLE_ROLLUP_2 UInt32, \n",
    "    ROLE_DEPTNAME UInt32, \n",
    "    ROLE_TITLE UInt32, \n",
    "    ROLE_FAMILY_DESC UInt32, \n",
    "    ROLE_FAMILY UInt32, \n",
    "    ROLE_CODE UInt32\n",
    ")\n",
    "ENGINE = MergeTree(date, date, 8192)\n",
    "```\n",
    "Вставлять данные будем в потоковом режиме с использованием командной строки Linux. Выходим из ClickHouse при помощи Crtl+C или написав команду \"exit\" (также работают команды \"quit\", \"logout\", \"учше\", \"йгше\", \"дщпщге\", \"exit;\", \"quit;\", \"logout;\", \"учшеж\", \"йгшеж\", \"дщпщгеж\", \"q\", \"й\", \"q\", \"Q\", \":q\", \"й\", \"Й\", \"Жй\"). Теперь набираем в консоли:\n",
    "```bash\n",
    "$ clickhouse client --host 127.0.0.1 --query 'INSERT INTO amazon_train FORMAT CSVWithNames' < ~/amazon/train.csv\n",
    "```\n",
    "Проверим, что данные загрузились\n",
    "\n",
    "```bash\n",
    "$ clickhouse client --host 127.0.0.1\n",
    "```\n",
    "```SQL\n",
    ":) SELECT count() FROM amazon_train\n",
    "```\n",
    "Посчитаем среднее значение по столбцу ACTION\n",
    "```SQL\n",
    ":) SELECT avg(ACTION) FROM amazon_train\n",
    "```\n",
    "\n",
    "### Работа с обученной моделью\n",
    "\n",
    "Создаем файл с конфигурацией модели\n",
    "```XML\n",
    "<models>\n",
    "    <model>\n",
    "        <!-- Model type. Now catboost only. -->\n",
    "        <type>catboost</type>\n",
    "        <!-- Model name. -->\n",
    "        <name>amazon</name>\n",
    "        <!-- Path to trained model. -->\n",
    "        <path>/home/catboost/tutorial/catboost_model.bin</path>\n",
    "        <!-- Update interval. -->\n",
    "        <lifetime>0</lifetime>\n",
    "    </model>\n",
    "</models>\n",
    "```\n",
    "В конфигурации ClickHouse уже прописан параметр\n",
    "```XML\n",
    "<models_config>/home/catboost/models/*_model.xml</models_config>\n",
    "```\n",
    "В этом можно убедиться, выполнив команду `tail /etc/clickhouse-server/config.xml`\n",
    "\n",
    "Проверим, что модель работает. Посмотрим на предсказания для первых 10 строк таблицы.\n",
    "```SQL\n",
    ":) SELECT \n",
    "    modelEvaluate('amazon', \n",
    "                  RESOURCE,\n",
    "                  MGR_ID,\n",
    "                  ROLE_ROLLUP_1,\n",
    "                  ROLE_ROLLUP_2,\n",
    "                  ROLE_DEPTNAME,\n",
    "                  ROLE_TITLE,\n",
    "                  ROLE_FAMILY_DESC,\n",
    "                  ROLE_FAMILY,\n",
    "                  ROLE_CODE) > 0 AS prediction, \n",
    "    ACTION AS target\n",
    "FROM amazon_train\n",
    "LIMIT 10\n",
    "```\n",
    "Теперь посмотрим на предсказанную вероятность\n",
    "```SQL\n",
    ":) SELECT \n",
    "    modelEvaluate('amazon', \n",
    "                  RESOURCE,\n",
    "                  MGR_ID,\n",
    "                  ROLE_ROLLUP_1,\n",
    "                  ROLE_ROLLUP_2,\n",
    "                  ROLE_DEPTNAME,\n",
    "                  ROLE_TITLE,\n",
    "                  ROLE_FAMILY_DESC,\n",
    "                  ROLE_FAMILY,\n",
    "                  ROLE_CODE) AS prediction,\n",
    "    1. / (1 + exp(-prediction)) AS probability, \n",
    "    ACTION AS target\n",
    "FROM amazon_train\n",
    "LIMIT 10\n",
    "```\n",
    "Посчитаем LogLoss на всей выборке\n",
    "```SQL\n",
    ":) SELECT -avg(tg * log(prob) + (1 - tg) * log(1 - prob)) AS logloss\n",
    "FROM \n",
    "(\n",
    "    SELECT \n",
    "        modelEvaluate('amazon', \n",
    "                      RESOURCE,\n",
    "                      MGR_ID,\n",
    "                      ROLE_ROLLUP_1,\n",
    "                      ROLE_ROLLUP_2,\n",
    "                      ROLE_DEPTNAME,\n",
    "                      ROLE_TITLE,\n",
    "                      ROLE_FAMILY_DESC,\n",
    "                      ROLE_FAMILY,\n",
    "                      ROLE_CODE) AS prediction,\n",
    "        1. / (1. + exp(-prediction)) AS prob, \n",
    "        ACTION AS tg\n",
    "    FROM amazon_train\n",
    ")\n",
    "```\n",
    "\n",
    "## Использование пула CatBoost\n",
    "\n",
    "Для тестовой выборки не будем создавать таблицу, а вместо этого воспользуемся табличной функцией `catBoostPool`.\n",
    "Описание формата столбцов пула находится в файле `/home/catboost/tutorial/amazon/test.cd` и выглядит так:\n",
    "\n",
    "```\n",
    "0\tDocId\tid\n",
    "1\tCateg\tRESOURCE\n",
    "2\tCateg\tMGR_ID\n",
    "3\tCateg\tROLE_ROLLUP_1\n",
    "4\tCateg\tROLE_ROLLUP_2\n",
    "5\tCateg\tROLE_DEPTNAME\n",
    "6\tCateg\tROLE_TITLE\n",
    "7\tCateg\tROLE_FAMILY_DESC\n",
    "8\tCateg\tROLE_FAMILY\n",
    "9\tCateg\tROLE_CODE\n",
    "```\n",
    "\n",
    "Посмотрим на структуру временной таблицы, которую создает catBoostPool\n",
    "```SQL\n",
    ":) DESCRIBE TABLE catBoostPool('amazon/test.cd', 'amazon/test.tsv')\n",
    "\n",
    "┌─name─────────────┬─type───┬─default_type─┬─default_expression─┐\n",
    "│ Categ0           │ String │              │                    │\n",
    "│ Categ1           │ String │              │                    │\n",
    "│ Categ2           │ String │              │                    │\n",
    "│ Categ3           │ String │              │                    │\n",
    "│ Categ4           │ String │              │                    │\n",
    "│ Categ5           │ String │              │                    │\n",
    "│ Categ6           │ String │              │                    │\n",
    "│ Categ7           │ String │              │                    │\n",
    "│ Categ8           │ String │              │                    │\n",
    "│ DocId            │ String │              │                    │\n",
    "│ id               │ String │ ALIAS        │ DocId              │\n",
    "│ RESOURCE         │ String │ ALIAS        │ Categ0             │\n",
    "│ MGR_ID           │ String │ ALIAS        │ Categ1             │\n",
    "│ ROLE_ROLLUP_1    │ String │ ALIAS        │ Categ2             │\n",
    "│ ROLE_ROLLUP_2    │ String │ ALIAS        │ Categ3             │\n",
    "│ ROLE_DEPTNAME    │ String │ ALIAS        │ Categ4             │\n",
    "│ ROLE_TITLE       │ String │ ALIAS        │ Categ5             │\n",
    "│ ROLE_FAMILY_DESC │ String │ ALIAS        │ Categ6             │\n",
    "│ ROLE_FAMILY      │ String │ ALIAS        │ Categ7             │\n",
    "│ ROLE_CODE        │ String │ ALIAS        │ Categ8             │\n",
    "└──────────────────┴────────┴──────────────┴────────────────────┘\n",
    "```\n",
    "Таблица содержит столбцы со значениями признаков и алиасы с именами из файла описания столбцов.\n",
    "Посчитаем вероятность для первых 10 строк\n",
    "```SQL\n",
    ":) SELECT \n",
    "    id,\n",
    "    modelEvaluate('amazon', *) AS prediction,\n",
    "    1. / (1. + exp(-prediction)) AS probability\n",
    "FROM catBoostPool('amazon/test.cd', 'amazon/test.tsv')\n",
    "LIMIT 10\n",
    "```\n",
    "При чтении из catBoostPool достаточно указать __*__ в качестве аргументов функции `catBoostPool`\n",
    "Посчитаем ответ для всей выборки и запишем результат в файл.\n",
    "```SQL\n",
    "SELECT \n",
    "    toUInt32(id) AS Id, \n",
    "    1. / (1 + exp(-modelEvaluate('amazon', *))) AS Action\n",
    "FROM catBoostPool('amazon/test.cd', 'amazon/test.tsv') \n",
    "INTO OUTFILE 'submission.tsv'\n",
    "FORMAT CSVWithNames\n",
    "```\n",
    "Сделаем посылку на [Kaggle](https://www.kaggle.com/c/amazon-employee-access-challenge/leaderboard) (файл submission.tsv можно скачать из Docker контейнера при помощи интерфейса jupyter). Какое место получилось занять у вас?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
